package myJDBC.lowLevel;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.OracleTypes;
import myJDBC.*;
public class ObjectTypes {
			private	static ArrayList<String> recursForROT(int objTypeId, ArrayList<Integer> unbinded) {
				ArrayList<String> al = new ArrayList<String>();
				int parentId=0;
				StringBuilder selectObjects = new StringBuilder();
				  selectObjects.append("select o1.\"object_type_id\", o1.\"name\", o2.\"name\", o1.\"parent_id\" ")
						       .append("from \"object_type\" o1 ")
						       .append("left join( ")
						       .append("select \"object_type_id\", \"name\" ")
					           .append("from \"object_type\" ")
						       .append(") o2 ")
						       .append("on o1.\"parent_id\"=o2.\"object_type_id\" where o1.\"object_type_id\"=? ");
				if (myConnection.dbConnection != null) {
			 		   try (PreparedStatement pstmt = myConnection.dbConnection
				                .prepareStatement(selectObjects.toString())) {
						   pstmt.setInt(1, objTypeId);
						   pstmt.executeUpdate(); 
						   ResultSet rs = pstmt.executeQuery();
						String tmp;
						while (rs.next()) {
							tmp = rs.getString(1);
							al.add(tmp);
							tmp = rs.getString(2);
							al.add(tmp);
							tmp = rs.getString(3);
							al.add(tmp);
							parentId=rs.getInt(4);
						}
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					}
					ArrayList<String> tmp;
					ArrayList<Integer> listOfAttrId= AttrObjectTypes.returnAttrObjectTypes(objTypeId);
					for(int i=0; i<listOfAttrId.size();i=i+2){
						tmp=Attributes.returnAttribute(listOfAttrId.get(i));
						if(listOfAttrId.get(i+1)==2){
							unbinded.add(listOfAttrId.get(i));
						}else
						//	if((!imparent)||(listOfAttrId.get(i+1)!=Integer.parseInt(Binds.returnBind("private"))))
							if(listOfAttrId.get(i+1)!=1)
							{
								if(!unbinded.contains(Integer.parseInt(tmp.get(0)))){
									al.add(tmp.get(0));
									al.add(tmp.get(1));
									al.add(tmp.get(2));//bind_id
								}else
									unbinded.remove(tmp.get(0));
							}
					}
					if(parentId==0)
						return al;
					else{
						ArrayList<String> tmp2=recursForROT(parentId, unbinded);
						tmp2.remove(0);
						tmp2.remove(0);
						al.addAll(3, tmp2);
					}
				}else
					return null;
				return al;
			}
	
			//НЕ ВОЗВРАЩАЕТ ПРИВАТНЫЕ ПОЛЯ
			//obj_type_id, obj_type_name, parent1, parent2..., NULL, parentAttribute1, 2, ..., attribute1, 2,...
			public static ArrayList<String> returnObjectType(int objTypeId) {
				return recursForROT(objTypeId, new ArrayList<Integer>());
			}

			//obj_type_id, obj_type_name, parent_name
			public static ArrayList<String> returnObjectTypes() {
			ArrayList<String> al = new ArrayList<String>();
			StringBuilder selectObjects = new StringBuilder();
			  selectObjects.append("select o1.\"object_type_id\", o1.\"name\", o2.\"name\" ")
						   .append("from \"object_type\" o1 ")
						   .append("left join( ")
						   .append("select \"object_type_id\", \"name\" ")
						   .append("from \"object_type\" ")
						   .append(") o2 ")
						   .append("on o1.\"parent_id\"=o2.\"object_type_id\"");
				if (myConnection.dbConnection != null) {
					try {
						Statement statement = myConnection.dbConnection.createStatement();
						ResultSet rs = statement.executeQuery(selectObjects.toString());
						String tmp;
						while (rs.next()) {
							tmp = rs.getString(1);
							al.add(tmp);
							tmp = rs.getString(2);
							al.add(tmp);
							tmp = rs.getString(3);
							al.add(tmp);
						}
					} catch (SQLException e) {
						System.out.println(e.getMessage());
					}
					return al;
				} else
					return null;			
			}

			public static int createObjectType(String name, int parId) throws SQLException{
				int i=-1;
				CallableStatement  cs= null;
				if(parId==-1){
					try {
						StringBuilder plsql = new StringBuilder();
								  plsql.append(" begin ")
						               .append("insert into \"object_type\" (\"name\") values (?)")
						               .append("RETURNING \"object_type_id\" ")
						               .append("INTO ?; ")
						               .append("END;");
						cs=myConnection.dbConnection.prepareCall(plsql.toString());
						cs.setString(1, name);
						cs.registerOutParameter(2, OracleTypes.NUMBER);
						cs.execute();
						i=cs.getInt(2);
					} finally {
						if (cs != null)
							cs.close();
					}	
				}else{
					try {
						StringBuilder plsql = new StringBuilder();
						   plsql.append(" begin ")
						        .append("insert into \"object_type\" (\"name\", \"parent_id\") values (?,?)")
								.append("RETURNING \"object_type_id\" ")
								.append("INTO ?; ")
								.append("END;");
						cs=myConnection.dbConnection.prepareCall(plsql.toString());
						cs.setString(1, name);
						cs.setInt(2, parId);
						cs.registerOutParameter(3, OracleTypes.NUMBER);
						cs.execute();
						i=cs.getInt(3);
					} finally {
						if (cs != null)
							cs.close();
					}	
				}
			return i;
			}

				
			public static String returnObjTypeName(int id) throws SQLException{
				String tmp="";
				StringBuilder selectAttributes = new StringBuilder();
				   selectAttributes.append("select \"name\" ")
								   .append("from \"object_type\" ")
								   .append("where \"object_type_id\"=? ");
				PreparedStatement pstmt = null;
				try {
					pstmt = myConnection.dbConnection.prepareStatement(selectAttributes.toString());
					pstmt.setInt(1, id);
					pstmt.executeUpdate();
					ResultSet rs = pstmt.executeQuery();			
					while (rs.next()) {
						tmp = rs.getString(1);			
					}
				} finally {
					if (pstmt != null)
						pstmt.close();
				}
				return tmp;
			}

			public static ArrayList<String> returnObjectTypes_Id_Name_ParId() {
				ArrayList<String> al = new ArrayList<String>();
				StringBuilder selectObjects = new StringBuilder();
				  selectObjects.append("select o1.\"object_type_id\", o1.\"name\", o2.\"object_type_id\" ")
							   .append("from \"object_type\" o1 ")
							   .append("left join( ")
							   .append("select \"object_type_id\" ")
							   .append("from \"object_type\" ")
							   .append(") o2 ")
							   .append("on o1.\"parent_id\"=o2.\"object_type_id\"");
					if (myConnection.dbConnection != null) {
						try (Statement statement = myConnection.dbConnection.createStatement()) {
							ResultSet rs = statement.executeQuery(selectObjects.toString());
							String tmp;
							while (rs.next()) {
								tmp = rs.getString(1);
								al.add(tmp);
								tmp = rs.getString(2);
								al.add(tmp);
								tmp = rs.getString(3);
								al.add(tmp);
							}
						} catch (SQLException e) {
							System.out.println(e.getMessage());
						}
						return al;
					} else
						return null;			
				}
			

}
